package com.plugin.manage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.plugin.core.model.entity.ElderCheckOut;
import com.plugin.manage.pojo.vo.ElderCheckoutApplymentVO;
import com.plugin.manage.pojo.vo.ElderCheckoutDynamicVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 退住记录Mapper接口
 *
 * @author mark
 * @date 2025-08-04
 */
@Mapper
public interface CheckoutMapper extends BaseMapper<ElderCheckOut> {

    /**
     * 统计所有退住记录数量（不筛选approval_status）
     * 
     * @return 所有退住记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_check_out WHERE del = 0")
    Long countTotalCheckouts();

    /**
     * 统计approval_status=1的退住记录数量
     * 
     * @return approval_status=1的退住记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_check_out WHERE del = 0 AND approval_status = 1")
    Long countStatus1Checkouts();
    
    /**
     * 统计approval_status=2的退住记录数量
     * 
     * @return approval_status=2的退住记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_check_out WHERE del = 0 AND approval_status = 2")
    Long countStatus2Checkouts();
    
    /**
     * 统计approval_status=3的退住记录数量
     * 
     * @return approval_status=3的退住记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_check_out WHERE del = 0 AND approval_status = 3")
    Long countStatus3Checkouts();
    
    /**
     * 统计approval_status=4的退住记录数量
     * 
     * @return approval_status=4的退住记录数量
     */
    @Select("SELECT COUNT(*) FROM elder_check_out WHERE del = 0 AND approval_status = 4")
    Long countStatus4Checkouts();
    
    /**
     * 使用CASE WHEN一次性统计所有approval_status的记录数量
     * 
     * @return 包含所有approval_status数量的Map
     */
    @Select("SELECT " +
            "SUM(CASE WHEN approval_status = 1 THEN 1 ELSE 0 END) as status1Count, " +
            "SUM(CASE WHEN approval_status = 2 THEN 1 ELSE 0 END) as status2Count, " +
            "SUM(CASE WHEN approval_status = 3 THEN 1 ELSE 0 END) as status3Count, " +
            "SUM(CASE WHEN approval_status = 4 THEN 1 ELSE 0 END) as status4Count " +
            "FROM elder_check_out WHERE del = 0 AND end_flag = 0")
    Map<String, Long> countAllStatusCheckouts();
    
    /**
     * 查询长者姓名和退住状态
     * 
     * @return 长者姓名和退住状态列表
     */
    @Select("SELECT ei.realname, eco.approval_status as status " +
            "FROM elder_check_out eco " +
            "JOIN elder_info ei ON eco.elder_id = ei.id " +
            "WHERE eco.del = 0 AND ei.del = 0 AND eco.end_flag = 0")
    List<ElderCheckoutDynamicVO> queryElderNameAndStatus();

    /**
     * 查询退住申请详情
     * 
     * @param realname 长者姓名（可选）
     * @param startTime 开始时间（可选）
     * @param endTime 结束时间（可选）
     * @param sex 性别（可选）
     * @param roomNumber 房间号（可选）
     * @return 退住申请详情列表
     */
    @Select("<script>" +
            "SELECT eco.id, ei.realname, ei.age, ei.sex, eco.room_number, eco.create_time, " +
            "ei.emgency_name, ei.emgency_phone, ei.relation, eco.check_out_date, " +
            "eco.check_out_reason, ei.care_level, ei.health_label, ec.pay_amount " +
            "FROM elder_check_out eco " +
            "JOIN elder_info ei ON eco.elder_id = ei.id " +
            "LEFT JOIN elder_contact ec ON eco.contact_id = ec.id " +
            "WHERE eco.del = 0 AND ei.del = 0 AND eco.end_flag = 0 " +
            "<if test='realname != null and realname != \"\"'> AND ei.realname LIKE CONCAT('%', #{realname}, '%') </if> " +
            "<if test='startTime != null'> AND eco.create_time &gt;= #{startTime} </if> " +
            "<if test='endTime != null'> AND eco.create_time &lt;= #{endTime} </if> " +
            "<if test='sex != null'> AND ei.sex = #{sex} </if> " +
            "<if test='roomNumber != null and roomNumber != \"\"'> AND eco.room_number LIKE CONCAT('%', #{roomNumber}, '%') </if> " +
            "ORDER BY eco.create_time DESC" +
            "</script>")
    Page<ElderCheckoutApplymentVO> queryCheckoutApplyments(Page page,
                                                           @Param("realname") String realname,
                                                           @Param("startTime") LocalDateTime startTime,
                                                           @Param("endTime") LocalDateTime endTime,
                                                           @Param("sex") Integer sex,
                                                           @Param("roomNumber") String roomNumber);



}
